﻿Imports System.Data.OleDb

Public Class Details
    Inherits System.Web.UI.Page


    Protected Sub btn_comment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_comment.Click

        'Connect to the database
        Dim sql As String = "INSERT INTO comment (Author, Title, Comment, commentDate, Spirit_fkid, diner_fkid) VALUES (@f1, @f2, @f3, @f4, @f5, @f6)"     'this is the SQL statement
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        'The follow statment can put the information in TextBox into the database(Comment table)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)      'This will get the user's name from database, users do not need to input the name again here
        cmd.Parameters.AddWithValue("@f2", tb_title.Text)
        cmd.Parameters.AddWithValue("@f3", tb_comment.Text)
        cmd.Parameters.AddWithValue("@f4", Format(Now(), "yyyy-MM-dd ") & Format(Now(), "HH:mm:ss"))
        cmd.Parameters.AddWithValue("@f5", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f6", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()

        'Reload this page(details.aspx)
        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This is for TAG part
        If IsPostBack Then Return 'This mean, if it is a post back
        Dim sql As String = "INSERT INTO SpiritViewedByUser (spirit_fkid, user_fkid) VALUES (@f1, @f2)"     'this is the SQL statement
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        conn.Open()
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", Membership.GetUser(User.Identity.Name).ProviderUserKey)     'Add the user id into the table
        cmd.ExecuteNonQuery()
        conn.Close()


        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This is for load user name and upload time
        Dim sqlUserTime As String = "SELECT * FROM SpiritList WHERE ID=@f1"
        Dim connUserTime As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmdUserTime As OleDbCommand = New OleDbCommand(sqlUserTime, connUserTime)

        connUserTime.Open()
        cmdUserTime.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dbReaderUserTime = cmdUserTime.ExecuteReader()

        dbReaderUserTime.Read()

        If Not IsDBNull(dbReaderUserTime("diner_fkid")) Then
            lb_uploadName.Text = dbReaderUserTime("diner_fkid")
        End If
        If Not IsDBNull(dbReaderUserTime("uploadDate")) Then
            lb_uploadTime.Text = dbReaderUserTime("uploadDate")
        End If

        hpl_uploadUser.NavigateUrl = "ViewProfile.aspx?dinerID=" & dbReaderUserTime("diner_fkid")

        connUserTime.Close()


        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This is for RATING part
        Dim sqlRating As String = "SELECT * FROM SpiritRatingJunction WHERE spirit_fkid=@f1 AND diner_fkid=@f2"
        Dim connRating As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmdRating As OleDbCommand = New OleDbCommand(sqlRating, connRating)

        connRating.Open()
        cmdRating.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmdRating.Parameters.AddWithValue("@f2", User.Identity.Name)
        Dim dbRaderRating = cmdRating.ExecuteReader()

        dbRaderRating.Read()

        If dbRaderRating.HasRows Then                                       'If the use rated the spirit before...
            If Not IsDBNull(dbRaderRating("rating")) Then
                rt_userRating.CurrentRating = dbRaderRating("rating")       'Load the rating of a user gived
            End If

            If Not IsDBNull(dbRaderRating("rateOrNot")) And dbRaderRating("rateOrNot") = "True" Then        'To see the user rates the spirit or not.
                btn_rating.Visible = False          'If the user rated the spirit befor, the 'Rating' button will be hinden
                btn_ratingUpdate.Visible = True     'the 'Update the Rating' button will be appear.
            End If
        End If

        connRating.Close()


        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This statement is for the like function
        tb_like.Visible = True
        lb_countLike.Visible = False
        btn_cancelLike.Visible = False

        Dim sqlLike As String = "SELECT * FROM SpiritLikeJunction WHERE spirit_fkid=@f1 AND diner_fkid=@f2"
        Dim connLike As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmdLike As OleDbCommand = New OleDbCommand(sqlLike, connLike)

        connLike.Open()
        cmdLike.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmdLike.Parameters.AddWithValue("@f2", User.Identity.Name)
        Dim dbRaderLike = cmdLike.ExecuteReader()

        dbRaderLike.Read()

        If dbRaderLike.HasRows() Then       'This statement is to see weather the user has liked this spirit or not
            tb_like.Visible = False         'If the user has clicked the 'Like' button, they cannot the 'Like' button again
            lb_countLike.Visible = True     'and instead of that, they will see the Label and 'Cancel Like' button
            btn_cancelLike.Visible = True

            Dim sqlCount As String = "SELECT * FROM SpiritLikeCountView WHERE ID=@f1 "
            Dim connCount As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
            Dim cmdCount As OleDbCommand = New OleDbCommand(sqlCount, connCount)

            connCount.Open()
            cmdCount.Parameters.AddWithValue("@f1", Request.Params("ID"))
            Dim dbRaderLikeCount = cmdCount.ExecuteReader()

            dbRaderLikeCount.Read()
            lb_countLike.Text = "You have LIKED this spirit. There are " & dbRaderLikeCount("IDCount") & " people have liked this spirit"

            connCount.Close()

        End If

        connLike.Close()

    End Sub

    Protected Sub btn_addTag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_addTag.Click
        Dim spiritID = Request.Params("ID")     'spiritID will story the imformation of ID in SpiritList table
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)

        conn.Open()

        'Handle with the tags which are inputted by user in the TextBox
        If Not tb_newTags.Text.Trim() = "" Then             'If there is nothing in the 'tb_newTags', the system will not add the empty tag into the databse
            Dim inputTags = tb_newTags.Text.Split(",")      'Split new tags in the TextBox, which is inputted by users. The input
            For Each newTag As String In inputTags          'Go through Array of 'inputTags', and 'newTag' is used to stor each new tag
                Dim alreadyInTagCmd As New OleDbCommand("SELECT * FROM Tag WHERE Tag=@f1", conn)        'Go through the table of 'Tag'
                alreadyInTagCmd.Parameters.AddWithValue("@f1", newTag)
                Dim oleDbReader = alreadyInTagCmd.ExecuteReader()       'oleDbReader are used to read the data in the database
                If Not oleDbReader.HasRows Then                         'If the new tag, which is inputted by users, is not in the database, then put the new tag into the databse
                    Dim addNewTagCmd As New OleDbCommand("INSERT INTO Tag (Tag) VALUES (@f1)", conn)
                    addNewTagCmd.Parameters.AddWithValue("@f1", newTag) 'Put the new tag into the database
                    addNewTagCmd.ExecuteNonQuery()
                End If
                'Find the ID of tag
                Dim searchTagIDCmd As New OleDbCommand("SELECT ID FROM Tag WHERE Tag=@f1", conn)
                searchTagIDCmd.Parameters.AddWithValue("@f1", newTag)
                Dim newTagOleDbReader = searchTagIDCmd.ExecuteReader()
                newTagOleDbReader.Read()
                Dim newID = newTagOleDbReader.Item("ID")        'Get the new ID
                'Associate tag's ID with spirit's ID
                Dim makeAssoCmd As New OleDbCommand("INSERT INTO SpiritTagJunction (tag_fkid, spirit_fkid) VALUES (@f1, @f2)", conn)
                makeAssoCmd.Parameters.AddWithValue("@f1", newID)
                makeAssoCmd.Parameters.AddWithValue("@f2", spiritID)
                makeAssoCmd.ExecuteNonQuery()
            Next
        End If

        For Each eachTag As ListItem In cbl_tags.Items     'Go through the items in the 'cbl_tags'
            If (eachTag.Selected = True) Then
                Dim cmd As New OleDbCommand("INSERT INTO SpiritTagJunction (tag_fkid, spirit_fkid) VALUES (@f1, @f2)", conn)     'If the tag in the 'cbl_tags' is selected, then 
                cmd.Parameters.AddWithValue("@f1", eachTag.Value)
                cmd.Parameters.AddWithValue("@f2", spiritID)
                cmd.ExecuteNonQuery()
            End If
        Next
        conn.Close()

        'Reload this page(details.aspx)
        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub btn_rating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_rating.Click

        'This button will handle the action of give a spirit a new rate. In other word, if user 
        'doesn't rating the spirit befor, they can use this button to rate a spirit.
        Dim sql As String = "INSERT INTO SpiritRatingJunction (rating, rateOrNot, spirit_fkid, diner_fkid) VALUES (@f1, @f2, @f3, @f4)"     'this is the SQL statement
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        conn.Open()
        cmd.Parameters.AddWithValue("@f1", rt_userRating.CurrentRating)     'Add the new rating into the database
        cmd.Parameters.AddWithValue("@f2", True)                            'Then set the rateOrNot to 'True'
        cmd.Parameters.AddWithValue("@f3", Request.Params("ID"))            'Add the spirit_fkid into the database
        cmd.Parameters.AddWithValue("@f4", User.Identity.Name)              'Add the diner_fkid into the database
        cmd.ExecuteNonQuery()

        conn.Close()

        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub btn_ratingUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_ratingUpdate.Click

        'This button is for update a rating. In other word, if the user rated the spirit before. But they want to
        'change the rating they gave. They can use this button.
        Dim sql As String = "UPDATE SpiritRatingJunction SET rating=@f1 WHERE spirit_fkid=@f2 AND diner_fkid=@f3"     'this is the SQL statement
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        conn.Open()
        cmd.Parameters.AddWithValue("@f1", rt_userRating.CurrentRating)     'Update the rating.
        cmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()

        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub tb_like_Click(ByVal sender As Object, ByVal e As EventArgs) Handles tb_like.Click

        Dim sql As String = "INSERT INTO SpiritLikeJunction (spirit_fkid, diner_fkid) VALUES (@f1, @f2)"     'this is the SQL statement
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        conn.Open()
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()

        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub btn_cancelLike_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_cancelLike.Click

        'This button can cancel the like. If the user does not like the apirit any more, they can click this button to cancel the like.
        Dim sql As String = "DELETE FROM SpiritLikeJunction WHERE spirit_fkid=@f1 AND diner_fkid=@f2"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        conn.Open()
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()

        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))

    End Sub
End Class